from flask_sqlalchemy import SQLAlchemy
from werkzeug.security import generate_password_hash, check_password_hash
from datetime import datetime

db = SQLAlchemy()

class User(db.Model):
    __tablename__ = 'users'
    
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    username = db.Column(db.String(50), unique=True, nullable=False)
    password = db.Column(db.String(255), nullable=False)
    age = db.Column(db.Integer)
    gender = db.Column(db.Enum('男', '女'))
    ethnicity = db.Column(db.String(50))
    mandarin_level = db.Column(db.Enum('一级甲等', '一级乙等', '二级甲等', '二级乙等', '三级甲等', '三级乙等'))
    role = db.Column(db.Enum('admin', 'user'), default='user')
    
    recordings = db.relationship('Recording', backref='user', lazy=True)
    assignments = db.relationship('UserSentenceAssignment', backref='user', lazy=True)
    
    def set_password(self, password):
        self.password = generate_password_hash(password)
        
    def check_password(self, password):
        return check_password_hash(self.password, password)
    
    def to_dict(self):
        return {
            'id': self.id,
            'username': self.username,
            'age': self.age,
            'gender': self.gender,
            'ethnicity': self.ethnicity,
            'mandarin_level': self.mandarin_level,
            'role': self.role
        }

class Sentence(db.Model):
    __tablename__ = 'sentences'
    
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    content = db.Column(db.Text, nullable=False)
    
    recordings = db.relationship('Recording', backref='sentence', lazy=True)
    assignments = db.relationship('UserSentenceAssignment', backref='sentence', lazy=True)
    
    def to_dict(self):
        return {
            'id': self.id,
            'content': self.content
        }

class Recording(db.Model):
    __tablename__ = 'recordings'
    
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
    sentence_id = db.Column(db.Integer, db.ForeignKey('sentences.id'), nullable=False)
    audio_file = db.Column(db.String(255), nullable=False)
    
    def to_dict(self):
        return {
            'id': self.id,
            'user_id': self.user_id,
            'sentence_id': self.sentence_id,
            'sentence_content': self.sentence.content,
            'audio_file': self.audio_file
        }

class UserSentenceAssignment(db.Model):
    """用户句子分配表 - 管理用户需要录制的句子"""
    __tablename__ = 'user_sentence_assignments'

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
    sentence_id = db.Column(db.Integer, db.ForeignKey('sentences.id'), nullable=False)
    sequence_order = db.Column(db.Integer, nullable=False)  # 录制顺序
    status = db.Column(db.Enum('pending', 'completed'), default='pending')
    assigned_at = db.Column(db.DateTime, default=datetime.utcnow)
    completed_at = db.Column(db.DateTime, nullable=True)

    # 确保同一用户不会被分配同一个句子多次
    __table_args__ = (
        db.UniqueConstraint('user_id', 'sentence_id', name='unique_user_sentence_assignment'),
        db.Index('idx_user_sequence', 'user_id', 'sequence_order'),
        db.Index('idx_user_status', 'user_id', 'status'),
    )

    def to_dict(self):
        return {
            'id': self.id,
            'user_id': self.user_id,
            'sentence_id': self.sentence_id,
            'sentence_content': self.sentence.content if self.sentence else None,
            'sequence_order': self.sequence_order,
            'status': self.status,
            'assigned_at': self.assigned_at.isoformat() if self.assigned_at else None,
            'completed_at': self.completed_at.isoformat() if self.completed_at else None
        }

    def mark_completed(self):
        """标记为已完成"""
        self.status = 'completed'
        self.completed_at = datetime.utcnow()
        db.session.commit()

class AssignmentTemplate(db.Model):
    """分配模板表 - 预设的句子分配方案"""
    __tablename__ = 'assignment_templates'

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(100), nullable=False)
    description = db.Column(db.Text)
    sentence_ids = db.Column(db.JSON, nullable=False)  # 存储句子ID列表
    category = db.Column(db.String(50))
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

    def to_dict(self):
        return {
            'id': self.id,
            'name': self.name,
            'description': self.description,
            'sentence_ids': self.sentence_ids,
            'category': self.category,
            'created_at': self.created_at.isoformat() if self.created_at else None,
            'sentence_count': len(self.sentence_ids) if self.sentence_ids else 0
        }